--set hive.mapred.mode ='proceed'
with t_center_deliver_detail as (
select
tt.waybill_no
,tt.apply_time --登记时间
,tt.apply_type_name
,tt.apply_type_code
,tt.examine_time --审核时间
,tt.apply_network_code  --登记网点code=申请网点idd
,tt.entry_type --录入类型 1拦截件2问题件
,os.order_source_name   --订单来源name
,tt.order_source_code   --订单来源code
,tt.cod_need --是否需要代收货款
,tt.pick_network_code     --寄件网点
,tt.collect_time          --揽收时间
,tt.dispatch_network_code --派件网点
,tt.problem_type     --问题件类型
,tt.problem_type_code --问题件类型code
,tt.difficult_scan_time --问题件扫描时间
,tt.arrival_time  --到件时间
,tt.abnormal_type --异常类型1不应退回2未按要求派送
,count(deliver.waybill_no) as deliver_cnt --派件次数
,tt.apply_source
,tt.customer_code
,tt.customer_name
,tt.abnomal_reback_type  --异常类行code
,tt.abnomal_reback_type_name --异常类型名称
,tt.dt
from
(
    select
     rt.waybill_no
    ,rt.apply_time --登记时间
    ,rt.apply_type_name
    ,rt.apply_type_code
    ,rt.examine_time --审核时间
    ,rt.apply_network_code  --登记网点code=申请网点idd
    ,rt.order_source_code   --订单来源code
    ,rt.cod_need--是否需要代收货款
    ,diff.problem_type     --问题件类型
    ,diff.problem_type_code --问题件类型code
    ,diff.difficult_scan_time --问题件扫描时间
    ,diff.abnormal_type --异常类型1不应退回2未按要求派送
    ,row_number() over(partition by rt.waybill_no,rt.dt order by rt.apply_time,difficult_scan_time desc) as rnk
    --,way.pick_network_code     --寄件网点
    ,way.collect_time          --揽收时间
    ,way.dispatch_network_code --派件网点
    ,rt.entry_type --录入类型 1拦截件2问题件
    ,arrival.arrival_time
    ,rt.sender_network_code as pick_network_code--寄件(揽收)网点
    ,rt.apply_source
    ,rt.customer_code
    ,rt.customer_name
    ,rt.abnomal_reback_type  --异常类行code
    ,rt.abnomal_reback_type_name --异常类型名称
    ,rt.dt
    from
    (   select
         waybill_no
        ,apply_time --登记时间
        ,apply_type_name
        ,apply_type_code
        ,examine_time --审核时间
        ,apply_network_code  --登记网点code=申请网点idd
        ,order_source_code   --订单来源code
        ,if(cod_need=1,1,0)  as cod_need--是否需要代收货款
        ,entry_type   --录入类型 1拦截件2问题件
        ,sender_network_code
        ,sender_network_name
        ,apply_source
        ,customer_code
        ,customer_name
        ,case when apply_source ='17' then 1
              when apply_source in ('6','11','12','13','14','18','21','23') then 2
              end as abnomal_reback_type  --异常类行code
        ,case when apply_source ='17' then '转寄超期自动退件'
              when apply_source in ('6','11','12','13','14','18','21','23') then '平台在途拦截退件'
              end as abnomal_reback_type_name  --异常类型名称
        ,date(apply_time) as dt
        from jms_dwd.dwd_tab_reback_transfer_express_base
        where date(apply_time) between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
        and !(apply_time<nvl(revoke_time,'1901-01-01 00:00:00')) --剔除登记时间小于撤销时间（剔除撤销）
    )rt
    left join
    ( --问题件扫描网点  退件登记前最晚的那条
        select
         d.waybill_no
        ,d.problem_type     --问题件类型
        ,d.problem_type_code --问题件类型code
        ,d.scan_time  as difficult_scan_time --问题件扫描时间
        ,a.abnormal_type --异常配置的异常类型
        from jms_dwd.dwd_tab_barscan_difficult_base_dt d
        left join jms_dim.dim_tab_abnormal_config_base a on a.bad_ship_code=d.problem_type_code
        where dt between date_add('{{ execution_date  | cst_ds }}',-31) and '{{ execution_date  | cst_ds }}'
    )diff on rt.waybill_no=diff.waybill_no and diff.difficult_scan_time<=rt.apply_time
    left join
    (--运单信息
        select
        waybill_no --运单号
        --,pick_network_code     --寄件网点
        ,collect_time          --揽收时间
        ,dispatch_network_code --派件网点
        from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
        where dt between  date_add('{{ execution_date  | cst_ds }}',-45)  and '{{ execution_date  | cst_ds }}'
    )way on rt.waybill_no=way.waybill_no
    left join
    ( --退件网点到件时间
        select
         waybill_no
        ,scan_site_code
        ,scan_time as arrival_time
        ,row_number() over(partition by waybill_no,scan_site_code order by scan_time asc) as rnk
        from jms_dwd.dwd_tab_barscan_arrival_base_dt
        where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
    )arrival on rt.waybill_no=arrival.waybill_no and rt.apply_network_code=arrival.scan_site_code  and
    arrival.rnk=1
)tt
left join
(--派件次数
    select waybill_no,scan_time as deliver_time
    from jms_dwd.dwd_tab_barscan_deliver_base_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
    group by waybill_no,scan_time
)deliver on tt.waybill_no=deliver.waybill_no and tt.apply_time>deliver.deliver_time
left join jms_dim.dim_tab_order_source_group os on os.order_source_code=tt.order_source_code
where tt.rnk=1
group by
          tt.waybill_no
         ,tt.apply_time --登记时间
         ,tt.apply_type_name
         ,tt.apply_type_code
         ,tt.examine_time --审核时间
         ,tt.apply_network_code  --登记网点code=申请网点idd
         ,tt.order_source_code   --订单来源code
         ,os.order_source_name   --订单来源name
         ,tt.cod_need --是否需要代收货款
         ,tt.pick_network_code     --寄件网点
         ,tt.collect_time          --揽收时间
         ,tt.dispatch_network_code --派件网点
         ,tt.problem_type     --问题件类型
         ,tt.problem_type_code --问题件类型code
         ,tt.difficult_scan_time --问题件扫描时间
         ,tt.arrival_time
         ,tt.abnormal_type
         ,tt.entry_type
         ,tt.dt
         ,tt.apply_source
         ,tt.customer_code
         ,tt.customer_name
         ,tt.abnomal_reback_type  --异常类行code
         ,tt.abnomal_reback_type_name --异常类型名称
)
insert overwrite table jms_tmp.tmp_reback_detail_mid_dt partition(dt)
select
    main.waybill_no--运单号
     ,main.apply_time--登记时间
     ,main.apply_type_name--异常类型名称
     ,main.apply_type_code--异常类型code
     ,main.examine_time--审核时间
     ,main.apply_network_code--登记网点code=申请网点idd
     ,net1.name as apply_network_name--登记网点name
     ,main.entry_type --录入类型 1拦截件2问题件
     ,main.order_source_code--订单来源code
     ,main.cod_need--是否需要代收货款
     ,main.pick_network_code  as taking_network_code--寄件网点code
     ,net3.name as taking_network_name --寄件网点名称
     ,net3.agent_code as taking_agent_code
     ,net3.agent_name as taking_agent_name
     ,main.collect_time   as taking_time--揽收时间
     ,main.dispatch_network_code --派件网点code
     ,net4.name as dispatch_network_name --派件网点
     ,main.problem_type--问题件类型
     ,main.problem_type_code--问题件类型code
     ,main.difficult_scan_time--问题件扫描时间
     ,main.arrival_time --到件时间
     ,main.abnormal_type--异常类型1不应退回2未按要求派送
     ,main.deliver_cnt --派件次数
     ,net1.network_type as apply_network_type --登记网点类型（4中心5集散6网点）
     ,main.order_source_name--订单来源name
     ,main.apply_source
     ,main.customer_code
     ,main.customer_name
     ,main.abnomal_reback_type  --异常类行code
     ,main.abnomal_reback_type_name --异常类型名称
     ,main.dt as dt
from t_center_deliver_detail main
         left join jms_dim.dim_network_whole_massage net1 on net1.code=main.apply_network_code
         left join jms_dim.dim_network_whole_massage net3 on net3.code=main.pick_network_code
         left join jms_dim.dim_network_whole_massage net4 on net4.code=main.dispatch_network_code
distribute by dt, pmod(hash(waybill_no), 4);
--------------------------------------------------------------------------------------------------------
insert overwrite table jms_dm.dm_reback_all_detail_dt partition(dt,detail_flag)
select
 waybill_no--运单号
,tt.apply_time--登记时间
,tt.examine_time--审核时间
,tt.apply_network_code--登记网点code
,tt.apply_network_name--登记网点name
,tt.order_source_code--订单来源code
,tt.cod_need--是否需要代收货款
,tt.taking_network_code--寄件网点code
,net.name as taking_network_name --寄件网点名称
,net.agent_code as taking_agent_code
,net.agent_name as taking_agent_name
,tt.taking_time--揽收时间
,tt.dispatch_network_code --派件网点code
,tt.dispatch_network_name --派件网点
,tt.problem_type--问题件类型
,tt.problem_type_code--问题件类型code
,tt.arrival_time --到件时间
,tt.abnormal_type--异常类型1不应退回2未按要求派送
,tt.deliver_cnt --派件次数
,tt.apply_network_type --登记网点类型（4中心5集散6网点)
,tt.is_reback  --是否退件 1 是0 否
,tt.detail_type  --明细类型 1退件登记日期2退件申请日期3揽收退件4揽收拦截
,tt.dt as date_time
,tt.order_source_name
,net2.agent_code as dispatch_agent_code --派件网点code
,net2.agent_name as dispatch_agent_name --派件网点编码
,net2.network_type as dispatch_network_type
,net.network_type as taking_network_type
,net3.agent_code as apply_agent_code
,net3.agent_name as apply_agent_name
,tt.customer_code
,tt.customer_name
,tt.abnomal_reback_type  --异常类行code
,tt.abnomal_reback_type_name --异常类型名称
,tt.dt
,tt.detail_flag  --明细类型1退件登记日期2退件申请日期3揽收退件4揽收拦截
from
(
    select
    waybill_no--运单号
    ,apply_time--登记时间
    ,examine_time--审核时间
    ,apply_network_code--登记网点code
    ,apply_network_name--登记网点name
    ,order_source_code--订单来源code
    ,cod_need--是否需要代收货款
    ,taking_network_code--寄件网点code
    ,taking_network_name --寄件网点名称
    ,taking_agent_code
    ,taking_agent_name
    ,taking_time--揽收时间
    ,dispatch_network_code --派件网点code
    ,dispatch_network_name --派件网点
    ,problem_type--问题件类型
    ,problem_type_code--问题件类型code
    ,arrival_time --到件时间
    ,abnormal_type--异常类型1不应退回2未按要求派送
    ,deliver_cnt --派件次数
    ,apply_network_type --登记网点类型（4中心5集散6网点)
    ,1 as rnk
    ---,row_number() over(partition by waybill_no,dt order by apply_time desc ) as rnk
    ,1 as is_reback
    ,1 as detail_type  --退件申请日期
    ,date(apply_time) as dt
    ,1 as detail_flag  --退件申请日期
    ,order_source_name
    ,customer_code
    ,customer_name
    ,abnomal_reback_type  --异常类行code
    ,abnomal_reback_type_name --异常类型名称
    from jms_tmp.tmp_reback_detail_mid_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-2) and '{{ execution_date  | cst_ds }}'
        and apply_type_code=4 --退回件
    and taking_network_code!=apply_network_code --剔除拦截

    union all

    select
    waybill_no--运单号
    ,apply_time--登记时间
    ,examine_time--审核时间
    ,apply_network_code--登记网点code
    ,apply_network_name--登记网点name
    ,order_source_code--订单来源code
    ,cod_need--是否需要代收货款
    ,taking_network_code--寄件网点code
    ,taking_network_name --寄件网点名称
    ,taking_agent_code
    ,taking_agent_name
    ,taking_time--揽收时间
    ,dispatch_network_code --派件网点code
    ,dispatch_network_name --派件网点
    ,problem_type--问题件类型
    ,problem_type_code--问题件类型code
    ,arrival_time --到件时间
    ,abnormal_type--异常类型1不应退回2未按要求派送
    ,deliver_cnt --派件次数
    ,apply_network_type --登记网点类型（4中心5集散6网点)
    ,1 as rnk
    --,row_number() over(partition by waybill_no,dt order by apply_time desc ) as rnk
    ,1 as is_reback
    ,2 as detail_type  --退件审核日期
    ,date(examine_time) as dt
    ,2 as detail_flag  --退件审核日期  --退件审核日期
    ,order_source_name
    ,customer_code
    ,customer_name
    ,abnomal_reback_type  --异常类行code
    ,abnomal_reback_type_name --异常类型名称
    from jms_tmp.tmp_reback_detail_mid_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-14) and '{{ execution_date  | cst_ds }}' and apply_type_code=4 --退回件
    and date(examine_time) between date_add('{{ execution_date  | cst_ds }}',-2) and '{{ execution_date  | cst_ds }}'
    and taking_network_code!=apply_network_code
    union all

    select
    t.waybill_no--运单号
    ,rd.apply_time--登记时间
    ,rd.examine_time--审核时间
    ,rd.apply_network_code--登记网点code
    ,rd.apply_network_name--登记网点name
    ,rd.order_source_code--订单来源code
    ,rd.cod_need--是否需要代收货款
    ,t.scan_site_code as taking_network_code--寄件网点code
    ,null as taking_network_name --寄件网点名称
    ,null as taking_agent_code
    ,null as taking_agent_name
    ,t.scan_time as taking_time--揽收时间
    ,rd.dispatch_network_code --派件网点code
    ,rd.dispatch_network_name --派件网点
    ,rd.problem_type--问题件类型
    ,rd.problem_type_code--问题件类型code
    ,rd.arrival_time --到件时间
    ,rd.abnormal_type--异常类型1不应退回2未按要求派送
    ,rd.deliver_cnt --派件次数
    ,rd.apply_network_type --登记网点类型（4中心5集散6网点)
    ,row_number() over(partition by t.waybill_no,date(t.scan_time) order by t.scan_time desc, rd.apply_time asc,t.dt asc ) as rnk
    ,if(rd.apply_time is not null,1,0) as  is_reback
    ,3 as detail_type  --揽收退件申请、审核日期
    ,date(t.scan_time) as dt
    ,3 as detail_flag
    ,rd.order_source_name
    ,rd.customer_code
    ,rd.customer_name
    ,rd.abnomal_reback_type  --异常类行code
    ,rd.abnomal_reback_type_name --异常类型名称
    from jms_dwd.dwd_tab_barscan_taking_base_dt t
    left join  jms_tmp.tmp_reback_detail_mid_dt rd on rd.dt between date_add('{{ execution_date  | cst_ds }}',-14) and '{{ execution_date  | cst_ds }}' and rd.apply_type_code=4 and rd.taking_network_code!=rd.apply_network_code--退回件 
    and t.waybill_no=rd.waybill_no  and  t.scan_time<=rd.apply_time
    where t.dt between date_add('{{ execution_date  | cst_ds }}',-14) and '{{ execution_date  | cst_ds }}'
    and date(t.scan_time) between date_add('{{ execution_date  | cst_ds }}',-14) and '{{ execution_date  | cst_ds }}'

    union all

    select
    t.waybill_no--运单号
    ,rd.apply_time--登记时间
    ,rd.examine_time--审核时间
    ,rd.apply_network_code--登记网点code
    ,rd.apply_network_name--登记网点name
    ,rd.order_source_code--订单来源code
    ,rd.cod_need--是否需要代收货款
    ,t.scan_site_code as taking_network_code--寄件网点code
    ,null as taking_network_name --寄件网点名称
    ,null as taking_agent_code
    ,null as taking_agent_name
    ,t.scan_time as taking_time--揽收时间
    ,rd.dispatch_network_code --派件网点code
    ,rd.dispatch_network_name --派件网点
    ,rd.problem_type--问题件类型
    ,rd.problem_type_code--问题件类型code
    ,rd.arrival_time --到件时间
    ,rd.abnormal_type--异常类型1不应退回2未按要求派送
    ,rd.deliver_cnt --派件次数
    ,rd.apply_network_type --登记网点类型（4中心5集散6网点)
    ,row_number() over(partition by t.waybill_no,date(t.scan_time) order by t.scan_time desc ,rd.apply_time asc,t.dt asc ) as rnk
    ,if(rd.apply_time is not null,1,0) as  is_reback
    ,4 as detail_type  --揽收拦截申请、审核日期
    ,date(t.scan_time) as dt
    ,4 as  detail_flag
    ,rd.order_source_name
    ,rd.customer_code
    ,rd.customer_name
    ,rd.abnomal_reback_type  --异常类行code
    ,rd.abnomal_reback_type_name --异常类型名称
    from jms_dwd.dwd_tab_barscan_taking_base_dt t
    left join jms_tmp.tmp_reback_detail_mid_dt rd on rd.dt between date_add('{{ execution_date  | cst_ds }}',-30) and '{{ execution_date  | cst_ds }}'
    and rd.entry_type=1 --拦截件
    and t.waybill_no=rd.waybill_no and  t.scan_time<=rd.apply_time
    where t.dt between date_add('{{ execution_date  | cst_ds }}',-29) and date_add('{{ execution_date  | cst_ds }}',-27)
    and date(t.scan_time) = date_add('{{ execution_date  | cst_ds }}',-29)
)tt
left join jms_dim.dim_network_whole_massage net on net.code=tt.taking_network_code
left join jms_dim.dim_network_whole_massage net2 on net2.code=tt.dispatch_network_code
left join jms_dim.dim_network_whole_massage net3 on net3.code=tt.apply_network_code
where rnk=1
distribute by dt,detail_flag,pmod(hash(waybill_no),44);
-------汇总-----------------------------------------------------------------------------------------------------
insert overwrite table jms_dm.dm_reback_all_cnt_dt partition(dt,cnt_flag)
SELECT
    tt.dt as date_time     --日期
     ,network_code  --网点编码
     ,net1.name as network_name
     ,net1.agent_code
     ,net1.agent_name
     ,tt.operate_num   --操作量 派件量，中转件量
     ,nvl(tt.reback_cnt,0) as reback_cnt    --退件量
     ,nvl(tt.reback_cod_cnt,0) as reback_cod_cnt --退件中cod量
     ,nvl(tt.not_reback_cnt,0) as not_reback_cnt --不用退回件量
     ,nvl(tt.error_deliver_cnt,0) as error_deliver_cnt --未按要求派送量
     ,nvl(tt.abnomal_cod_cnt,0) as abnomal_cod_cnt--异常退件中cod量
     ,case when tt.scan_type=4 and tt.detail_type=1 then 1  --中心退件率登记时间统计
           when tt.scan_type=4 and tt.detail_type=2 then 2  --中心退件率审核时间统计
           when tt.scan_type=6 and tt.detail_type=1 then 3  --派件退件率登记时间统计
           when tt.scan_type=6 and tt.detail_type=2 then 4  --派件退件率审核时间统计
           when tt.scan_type=5 and tt.detail_type=1 and tt.is_intercept=1 then 7  --揽收拦截登记时间统计
           when tt.scan_type=5 and tt.detail_type=2 and tt.is_intercept=1 then 8  --揽收拦截登记时间统计
           when tt.scan_type=5 and tt.detail_type=1 then 5  --揽收退件率登记时间统计
           when tt.scan_type=5 and tt.detail_type=2 then 6  --揽收退件率审核时间统计

    end as cnt_type
     ,net1.network_type --网点类型：网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
     ,tt.detail_type       --明细类型 1登记 2申请
     ,tt.scan_type         --扫描类型 6派件5揽收4中心转运
     ,tt.is_intercept      --揽收是否拦截
     ,tt.abnomal_reback_type
     ,tt.abnomal_reback_type_name
     ,dt
     ,case when tt.scan_type=4 and tt.detail_type=1 then 1  --中心退件率登记时间统计
           when tt.scan_type=4 and tt.detail_type=2 then 2  --中心退件率审核时间统计
           when tt.scan_type=6 and tt.detail_type=1 then 3  --派件退件率登记时间统计
           when tt.scan_type=6 and tt.detail_type=2 then 4  --派件退件率审核时间统计
           when tt.scan_type=5 and tt.detail_type=1 and tt.is_intercept=1 then 7  --揽收退件率登记时间统计拦截
           when tt.scan_type=5 and tt.detail_type=2 and tt.is_intercept=1 then 8  --揽收退件率登记时间统计
           when tt.scan_type=5 and tt.detail_type=1 then 5  --揽收退件率登记时间统计
           when tt.scan_type=5 and tt.detail_type=2 then 6  --揽收退件率审核时间统计

    end as cnt_flag
FROM
    (
        --中心转运件量\派件量揽收登记和审核统计
        select
            nvl(t2.dt,t1.dt) as date_time     --日期
             ,nvl(t2.network_code,t1.network_code) as  network_code  --网点编码
             ,nvl(t1.operate_num,0)  as operate_num --操作量 派件量，中转件量
             ,nvl(t2.reback_cnt,0) as reback_cnt    --退件量
             ,nvl(t2.reback_cod_cnt,0) as reback_cod_cnt --退件中cod量
             ,nvl(t2.not_reback_cnt,0) as not_reback_cnt --不用退回件量
             ,nvl(t2.error_deliver_cnt,0) as error_deliver_cnt --未按要求派送量
             ,nvl(t2.abnomal_cod_cnt,0) as abnomal_cod_cnt--异常退件中cod量
             ,nvl(t2.scan_type,t1.scan_type) as scan_type --类型4中心 6派件
             ,nvl(t2.detail_type,1) as detail_type
             ,0 as is_intercept
             ,t2.abnomal_reback_type
             ,t2.abnomal_reback_type_name
             ,nvl(t2.dt,t1.dt)  as dt
        from
            (
                SELECT
                    dt
                     ,apply_network_code as network_code
                     ,count(1) as reback_cnt --退件票数
                     ,count(if(cod_need=1,waybill_no,null)) as reback_cod_cnt --退件cod票数
                     ,count(if(abnormal_type=1,waybill_no,null)) as not_reback_cnt --不应退回件票数
                     ,count(if(abnormal_type=2 and nvl(deliver_cnt,0)<3,waybill_no,null)) as error_deliver_cnt --未按要求派送
                     ,count(if(cod_need=1 and abnormal_type in (1,2),waybill_no,null)) as abnomal_cod_cnt --未按要求派送
                     ,detail_type
                     ,case when apply_network_type in (5,6) then 6
                           when apply_network_type =4 then 4
                    end as scan_type
                     ,apply_network_type
                     ,abnomal_reback_type
                     ,abnomal_reback_type_name
                from  jms_dm.dm_reback_all_detail_dt
                where dt between date_add('{{ execution_date  | cst_ds }}',-2) and '{{ execution_date  | cst_ds }}'
                  and detail_type in (1,2) and apply_network_type in (4,5,6)
                group by apply_network_code,detail_type,dt,apply_network_type,abnomal_reback_type,abnomal_reback_type_name
            )t2
                full join
            (   --派件量
                SELECT
                    dt
                     ,network_code
                     ,sum(metric_value) as operate_num
                     ,6 as scan_type
                from jms_dwd.dwd_netwrok_metric_dictionary_dt
                where dt between date_add('{{ execution_date  | cst_ds }}',-2) and '{{ execution_date  | cst_ds }}'
                  and (metric_code ='tab_deliver' or (metric_code ='tab_send' and network_type='5'))
                group by  metric_name ,dt,network_code

                union all

                --中转货量
                select
                    dt
                     , scan_site_code as network_code
                     , sum(oper_cnt) operate_num
                     , 4 as scan_type
                from jms_dm.dm_center_transfer_summary_new_dt --小时任务 分区是 yyyy-MM-dd ,每个分区都包含最近三天的数据
                where dt between date_add('{{ execution_date  | cst_ds }}',-2) and '{{ execution_date  | cst_ds }}'
                  and sum_flag_code='zrr'  --自然日
                group by dt, scan_site_code

            )t1 on t2.network_code=t1.network_code and  t1.dt=t2.dt and t1.scan_type=t2.scan_type
        union all
        select
            date_time
             , taking_network_code
             , sum(operate_num) over(partition by dt,taking_network_code,scan_type,detail_type,is_intercept) as operate_num
             , reback_cnt        --退件票数
             , reback_cod_cnt    --退件cod票数
             , not_reback_cnt    --不应退回件票数
             , error_deliver_cnt --未按要求派送
             , abnomal_cod_cnt   --未按要求派送
             , scan_type         --揽收
             , detail_type
             , is_intercept
             , abnomal_reback_type
             , abnomal_reback_type_name
             , dt
        from (
        --揽收登记时间+审核时间
        SELECT
            dt as date_time
             ,taking_network_code
             ,count(1) as operate_num
             ,count(if(is_reback=1 ,waybill_no,null)) as reback_cnt --退件票数
             ,count(if(is_reback=1  and cod_need=1,waybill_no,null)) as reback_cod_cnt --退件cod票数
             ,count(if(is_reback=1  and abnormal_type=1,waybill_no,null)) as not_reback_cnt --不应退回件票数
             ,count(if(is_reback=1  and abnormal_type=2 and nvl(deliver_cnt,0)<3,waybill_no,null)) as error_deliver_cnt --未按要求派送
             ,count(if(is_reback=1  and cod_need=1 and abnormal_type in (1,2),waybill_no,null)) as abnomal_cod_cnt --未按要求派送
             ,5 as scan_type
             ,1 as detail_type
             ,0 as is_intercept
             ,abnomal_reback_type
             ,abnomal_reback_type_name
             ,dt
        from  jms_dm.dm_reback_all_detail_dt
        where dt between date_add('{{ execution_date  | cst_ds }}',-15) and '{{ execution_date  | cst_ds }}'
          and detail_type=3
        group by taking_network_code,detail_type,dt ,taking_network_type,abnomal_reback_type,abnomal_reback_type_name
        union all
        --揽收审核时间
        SELECT
            dt as date_time
             ,taking_network_code
             ,count(1) as operate_num
             ,count(if(is_reback=1 and examine_time is not null,waybill_no,null)) as reback_cnt --退件票数
             ,count(if(is_reback=1 and examine_time is not null and cod_need=1,waybill_no,null)) as reback_cod_cnt --退件cod票数
             ,count(if(is_reback=1 and examine_time is not null and abnormal_type=1,waybill_no,null)) as not_reback_cnt --不应退回件票数
             ,count(if(is_reback=1 and examine_time is not null and abnormal_type=2 and nvl(deliver_cnt,0)<3,waybill_no,null)) as error_deliver_cnt --未按要求派送
             ,count(if(is_reback=1 and examine_time is not null and cod_need=1 and abnormal_type in (1,2),waybill_no,null)) as abnomal_cod_cnt --未按要求派送
             ,5 as scan_type
             ,2 as detail_type
             ,0 as is_intercept
             ,abnomal_reback_type
             ,abnomal_reback_type_name
             ,dt
        from  jms_dm.dm_reback_all_detail_dt
        where dt between date_add('{{ execution_date  | cst_ds }}',-15) and '{{ execution_date  | cst_ds }}'
          and detail_type=3
        group by taking_network_code,detail_type,dt ,  taking_network_type,abnomal_reback_type,abnomal_reback_type_name
        union all
        --揽收拦截件
        SELECT dt                                                                                           as date_time
             , taking_network_code
             , count(1)                                                                                     as operate_num
             , count(if(is_reback = 1, waybill_no, null))                                                   as reback_cnt        --退件票数
             , count(if(is_reback = 1 and cod_need = 1, waybill_no, null))                                  as reback_cod_cnt    --退件cod票数
             , count(if(is_reback = 1 and abnormal_type = 1, waybill_no, null))                             as not_reback_cnt    --不应退回件票数
             , count(if(is_reback = 1 and abnormal_type = 2 and nvl(deliver_cnt, 0) < 3, waybill_no,null))  as error_deliver_cnt --未按要求派送
             , count(if(is_reback = 1 and cod_need = 1 and abnormal_type in (1, 2), waybill_no, null))      as abnomal_cod_cnt   --未按要求派送
             , 5                                                                                            as scan_type         --揽收
             , 1                                                                                            as detail_type
             , 1                                                                                            as is_intercept
             , abnomal_reback_type
             , abnomal_reback_type_name
             , dt
        from jms_dm.dm_reback_all_detail_dt
        where dt = date_add('{{ execution_date  | cst_ds }}', -29)
          and detail_type = 4
        group by taking_network_code, detail_type, dt, taking_network_type, abnomal_reback_type, abnomal_reback_type_name
        union all
        --揽收拦截件审核
        SELECT dt                                                                                                as date_time
             , taking_network_code
             , count(1)                                                                                          as operate_num
             , count(if(is_reback = 1 and examine_time is not null, waybill_no, null))                           as reback_cnt        --退件票数
             , count(if(is_reback = 1 and examine_time is not null and cod_need = 1, waybill_no,null))           as reback_cod_cnt    --退件cod票数
             , count(if(is_reback = 1 and examine_time is not null and abnormal_type = 1, waybill_no,null))      as not_reback_cnt    --不应退回件票数
             , count(if(is_reback = 1 and examine_time is not null and abnormal_type = 2 and nvl(deliver_cnt, 0) < 3, waybill_no, null)) as error_deliver_cnt --未按要求派送
             , count(if(is_reback = 1 and examine_time is not null and cod_need = 1 and abnormal_type in (1, 2),waybill_no,null))  as abnomal_cod_cnt   --未按要求派送
             , 5                                                                                                 as scan_type         --揽收
             , 2                                                                                                 as detail_type
             , 1                                                                                                 as is_intercept
             , abnomal_reback_type
             , abnomal_reback_type_name
             , dt
        from jms_dm.dm_reback_all_detail_dt
        where dt = date_add('{{ execution_date  | cst_ds }}', -29)
          and detail_type = 4
        group by taking_network_code, detail_type, dt, taking_network_type, abnomal_reback_type,abnomal_reback_type_name
        )tak
    )tt left join jms_dim.dim_network_whole_massage net1 on net1.code=tt.network_code
    distribute by dt,cnt_flag;